<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
    var $_total = null;
    var $_pagination = null;
    var $_num=null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('com_frleader.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }
	function getAll($list = null){
         //check user
        
        $aut=new bgAuthorize();
        //check center_Code for user login
         $group=$aut->getUserGroup();
         $u=JFactory::getUser();
         $u_ocba= $u->USEM_OCBA_CODE;                                
      /*  
       $qr = "SELECT DISTINCT COCH_EDATE,COCH_CODE,COCH_CD,COCH_NAME,COCH_KANA,COCH_OCBA_CODE,COCH_WOKF,COCH_SEX,COCH_UNIT,COCH_IMG,COCH_RDATE,COCH_MDATE,COCH_LDATE,
            		COCH_COMM,OCBA_NAME,COCH_LKDT_CODE_LIST	
             FROM  coch 
             LEFT JOIN (ocba INNER JOIN (town INNER JOIN (area INNER JOIN pref ON area.AREA_CODE=pref.PREF_AREA_CODE) ON town.TOWN_PREF_CODE =pref.PREF_CODE)
                                ON ocba.OCBA_TOWN_CODE=town.TOWN_CODE) ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE                                         
             LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE
             LEFT JOIN coli c ON coch.COCH_CODE = c.COLI_COCH_CODE
              ";*/
            
              $qr="SELECT DISTINCT COCH_LDATE,COCH_CODE,COCH_CD,COCH_NAME,COCH_KANA,COCH_OCBA_CODE,COCH_WOKF,COCH_SEX,COCH_UNIT,COCH_IMG,COCH_RDATE,COCH_MDATE,COCH_LDATE, COCH_COMM,OCBA_NAME,COCH_LKDT_CODE_LIST  
                    FROM coch LEFT JOIN (SELECT OCBA_CODE, OCBA_NAME,PREF_AREA_CODE,PREF_CODE FROM ocba 
                    INNER JOIN town ON OCBA_TOWN_CODE=TOWN_CODE 
                    INNER JOIN pref ON TOWN_PREF_CODE =PREF_CODE) AS ocba ON COCH_OCBA_CODE=OCBA_CODE
                    LEFT JOIN coli c ON coch.COCH_CODE = c.COLI_COCH_CODE
                    ";
        $where = array();
                           
        $where[] = "COCH_FLAG=1";
        $where[]=" (c.COLI_COCM_CODE = 1 OR c.COLI_COCM_CODE = 2) ";
        $where[]=" COCH_LDATE > NOW() ";
        
        //search are
        if(($list['area']) !=0 ){
                   $where[] = "PREF_AREA_CODE =$list[area]";  
        }
        //search pref
           if($list['pref'] != 0){
              $where[] = "PREF_CODE =$list[pref]";                      
          }
/*          if($list['pref'] != 0){
              $where[] = "PREF_AREA_CODE =$list[pref]";   
              } */
        //search name leader     
          if(!empty($list['nameld'])){
          $where[] = "COCH_NAME LIKE '%$list[nameld]%'";
         }
        //search term
           if(!empty($list['term'])){
            if(($list['term'])==0)
                {
                    $where[] = "";
                }
                else
                {
                    $where[] = "c.COLI_TERM = $list[term]";
                }
                
           }
           if(isset($list['wokf'])){
                $where[] = "COCH_WOKF =1";
            }
            
           //search unit
           if(isset($list['unit'])){
                if ($list['unit']==1)
                {
                    $where[] = "c.COLI_UNIT =1";
                }
                else if($list['unit']==2)
                {
                    $where[] = "c.COLI_UNIT =2";
                }
                else if($list['unit']==3)
                {
                    $where[] = "c.COLI_UNIT =3";
                }
            }
            //search leader qualification                   
                $wherestr="";
                for($i=1;$i<HelperBG::getIDAuto('cocm','COCM_CODE');$i++) {
                    if(isset($list['chb'.$i])){
                        if($wherestr==""){
                            $wherestr = $i;
                        }else{
                            $wherestr =$wherestr.",".$i;
                        }
                        
                }
                }
                if(!empty($wherestr)){

                    $where[] = "c.COLI_COCM_CODE in (".$wherestr.")";
                }
                
                //search qualification
                        $wherestrcb="";
                        for($i=1;$i<HelperBG::getIDAuto('lkdt','LKDT_CODE');$i++) {
                            if(isset($list['chl'.$i])){
                                if($wherestrcb==""){
                                    $wherestrcb = $i;
                                }else{
                                    $wherestrcb =$wherestrcb.",".$i;
                                }
                            }   
                        }
                      $where1=array();
                       if(($wherestrcb)!=null){
                            $ar=explode(',',$wherestrcb);
                            foreach ($ar as $value)
                                {
                                    $where1[] = "COCH_LKDT_CODE_LIST LIKE '%$value%'";
                                }
                            
                        }
                        if(!empty($where1)){
                            $where1 = implode(' OR ', $where1);
                             $where[]="(".$where1.")";
                        }
        
       if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
       // get total number record
       $this->_total = $this->getTotal($where);
    
         
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY COCH_DATE DESC, COCH_CODE DESC "; 
       //echo $qr;die;
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');  
        //load data
       //echo $qr;
       //die;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;         
	}
    
    //
    
    function getAllLeadColi()
    {
        $qr="select COCH_CODE,COLI_COCM_CODE
                from coch,coli
                where COCH_CODE=COLI_COCH_CODE
            ";
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList(); 
        return $result;
            
    }
    //get all data from Business cooperation
    function getAllHuman(){
	   $qr = "SELECT * FROM human";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}  
    
    //GET TOTAL RECORD
    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
           
            $query = "SELECT COUNT(DISTINCT COCH_CODE) FROM coch LEFT JOIN (SELECT OCBA_CODE, OCBA_NAME,PREF_AREA_CODE,PREF_CODE FROM ocba 
                    INNER JOIN town ON OCBA_TOWN_CODE=TOWN_CODE 
                    INNER JOIN pref ON TOWN_PREF_CODE =PREF_CODE) AS ocba ON COCH_OCBA_CODE=OCBA_CODE
                    LEFT JOIN coli c ON coch.COCH_CODE = c.COLI_COCH_CODE
                                 $where";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;

    }
    

    
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
  
    //load detail leader
    function load($cid){
        
        $query = "SELECT DISTINCT *,JOBM_NAME,COLI_UNIT,COLI_OCBA_CODE,OCBA_NAME
             FROM  coch LEFT JOIN (coli INNER JOIN cocm ON coli.COLI_COCM_CODE=cocm.COCM_CODE) ON coch.COCH_CODE = coli.COLI_COCH_CODE
            			LEFT JOIN ocba ON ocba.OCBA_CODE=coch.COCH_OCBA_CODE
                        LEFT JOIN jobm on JOBM_CODE=COCH_JOBM_CODE
                    WHERE  COCH_CODE=($cid)";
        //echo $query;          
        $this->_db->setQuery($query);
        $result = $this->_db->loadObject();
       // echo ($result->info_body);
        return $result;
        
    }
    //LOAD pref
    
    function getNamePref($id){
        $qr = "SELECT PREF_NAME FROM pref where PREF_CODE=$id";
       // order by
      // $qr .= " ORDER BY COCM_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadResult();
       return $result;
    }
    //load data qualification
    function loadcoli($cid)
    {
        $query="SELECT  *,COCM_NAME,OCBA_NAME
                        FROM cocm INNER JOIN coli ON cocm.COCM_CODE=coli.COLI_COCM_CODE 
                     			INNER JOIN ocba ON ocba.OCBA_CODE=coli.COLI_OCBA_CODE
                               WHERE coli.COLI_COCH_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
       // echo ($result->info_body);
        return $result;
        
    }
    
    //load data qualification
    function loadGroupColi($cid)
    {
        $query="SELECT  DISTINCT COCM_NAME
                        FROM cocm INNER JOIN coli ON cocm.COCM_CODE=coli.COLI_COCM_CODE 
                     			INNER JOIN ocba ON OCBA.OCBA_CODE=coli.COLI_OCBA_CODE
                               WHERE coli.COLI_COCH_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
       // echo ($result->info_body);
        return $result;
        
    }
    
    //load center,club 
    function getAllOCBA(){

	   $qr = "SELECT OCBA_CODE,OCBA_NAME FROM ocba";
       // order by
       $qr .= " ORDER BY OCBA_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	} 
    //load area
    function getAllArea(){

	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area ORDER BY AREA_CODE ASC";
       // order by
      // $qr .= " ORDER BY AREA_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	} 
    //load pref
    function getAllPref($area_code){
        if ($area_code==0)
        {
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";
        }
        else
        {
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;
        }
           $this->_db->setQuery($qr);
           $result = $this->_db->loadObjectList();
           
           return $result;
	}
    
    //get all data from Business cooperation
    function getAllLkdt(){

	   $qr = "SELECT * FROM lkdt";
       // order by
      // $qr .= " ORDER BY COCM_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}  
    function getTotalLkdt(){
        $qr = "SELECT count(*) FROM lkdt";
       // order by
      // $qr .= " ORDER BY COCM_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadResult();
       return $result;
    }
    

}
?>
